package zy.dao.sell.allocate.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.sell.allocate.SellAllocateDAO;
import zy.dto.common.BarcodeImportDto;
import zy.dto.common.ProductDto;
import zy.dto.sell.allocate.SellAllocateReportDto;
import zy.entity.base.product.T_Base_Product;
import zy.entity.base.size.T_Base_Size;
import zy.entity.sell.allocate.T_Sell_Allocate;
import zy.entity.sell.allocate.T_Sell_AllocateList;
import zy.entity.stock.data.T_Stock_DataBill;
import zy.util.CommonUtil;
import zy.util.DateUtil;
import zy.util.StringUtil;

@Repository
public class SellAllocateDAOImpl extends BaseDaoImpl implements SellAllocateDAO{
	
	@Override
	public Integer count(Map<String, Object> params) {
		Object ac_state = params.get("ac_state");
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object ac_out_shop = params.get("ac_out_shop");
		Object ac_in_shop = params.get("ac_in_shop");
		Object ac_out_dp = params.get("ac_out_dp");
		Object ac_in_dp = params.get("ac_in_dp");
		Object ac_man = params.get("ac_man");
		Object ac_number = params.get("ac_number");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1)");
		sql.append(" FROM t_sell_allocate t");
		sql.append(" WHERE 1=1");
		if (StringUtil.isNotEmpty(ac_state)) {
			sql.append(" AND ac_state = :ac_state ");
		}
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND ac_sysdate >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND ac_sysdate <= :enddate ");
		}
		if (StringUtil.isNotEmpty(ac_out_shop)) {
			sql.append(" AND ac_out_shop = :ac_out_shop ");
		}
		if (StringUtil.isNotEmpty(ac_in_shop)) {
			sql.append(" AND ac_in_shop = :ac_in_shop ");
		}
		if (StringUtil.isNotEmpty(ac_out_dp)) {
			sql.append(" AND ac_out_dp = :ac_out_dp ");
		}
		if (StringUtil.isNotEmpty(ac_in_dp)) {
			sql.append(" AND ac_in_dp = :ac_in_dp ");
		}
		if (StringUtil.isNotEmpty(ac_man)) {
			sql.append(" AND ac_man = :ac_man ");
		}
		if (StringUtil.isNotEmpty(ac_number)) {
			sql.append(" AND INSTR(ac_number,:ac_number) > 0 ");
		}
		sql.append(" AND (ac_out_shop = :shop_code OR ac_in_shop = :shop_code)");
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}

	@Override
	public List<T_Sell_Allocate> list(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		Object ac_state = params.get("ac_state");
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object ac_out_shop = params.get("ac_out_shop");
		Object ac_in_shop = params.get("ac_in_shop");
		Object ac_out_dp = params.get("ac_out_dp");
		Object ac_in_dp = params.get("ac_in_dp");
		Object ac_man = params.get("ac_man");
		Object ac_number = params.get("ac_number");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ac_id,ac_number,ac_date,ac_out_shop,ac_out_dp,ac_in_shop,ac_in_dp,ac_state,ac_sysdate,ac_man,");
		sql.append(" ac_receiver,ac_recedate,ac_sell_money,ac_cost_money,ac_amount,ac_in_sell_money,ac_in_cost_money,ac_remark,t.companyid,");
		sql.append(" (SELECT sp_name FROM t_base_shop sp WHERE sp_code = ac_out_shop AND sp.companyid = t.companyid LIMIT 1) AS outshop_name,");
		sql.append(" (SELECT sp_name FROM t_base_shop sp WHERE sp_code = ac_in_shop AND sp.companyid = t.companyid LIMIT 1) AS inshop_name");
		sql.append(" FROM t_sell_allocate t");
		sql.append(" WHERE 1=1");
		if (StringUtil.isNotEmpty(ac_state)) {
			sql.append(" AND ac_state = :ac_state ");
		}
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND ac_sysdate >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND ac_sysdate <= :enddate ");
		}
		if (StringUtil.isNotEmpty(ac_out_shop)) {
			sql.append(" AND ac_out_shop = :ac_out_shop ");
		}
		if (StringUtil.isNotEmpty(ac_in_shop)) {
			sql.append(" AND ac_in_shop = :ac_in_shop ");
		}
		if (StringUtil.isNotEmpty(ac_out_dp)) {
			sql.append(" AND ac_out_dp = :ac_out_dp ");
		}
		if (StringUtil.isNotEmpty(ac_in_dp)) {
			sql.append(" AND ac_in_dp = :ac_in_dp ");
		}
		if (StringUtil.isNotEmpty(ac_man)) {
			sql.append(" AND ac_man = :ac_man ");
		}
		if (StringUtil.isNotEmpty(ac_number)) {
			sql.append(" AND INSTR(ac_number,:ac_number) > 0 ");
		}
		sql.append(" AND (ac_out_shop = :shop_code OR ac_in_shop = :shop_code)");
		sql.append(" AND t.companyid=:companyid");
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY ac_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Sell_Allocate.class));
	}
	
	@Override
	public T_Sell_Allocate load(Integer ac_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ac_id,ac_number,ac_date,ac_out_shop,ac_out_dp,ac_in_shop,ac_in_dp,ac_state,ac_sysdate,ac_man,");
		sql.append(" ac_receiver,ac_recedate,ac_sell_money,ac_cost_money,ac_amount,ac_in_sell_money,ac_in_cost_money,ac_remark,t.companyid,");
		sql.append(" (SELECT sp_name FROM t_base_shop sp WHERE sp_code = ac_out_shop AND sp.companyid = t.companyid LIMIT 1) AS outshop_name,");
		sql.append(" (SELECT sp_name FROM t_base_shop sp WHERE sp_code = ac_in_shop AND sp.companyid = t.companyid LIMIT 1) AS inshop_name,");
		sql.append(" (SELECT dp_name FROM t_base_depot dp WHERE dp_code = ac_out_dp AND dp.companyid = t.companyid LIMIT 1) AS outdepot_name,");
		sql.append(" (SELECT dp_name FROM t_base_depot dp WHERE dp_code = ac_in_dp AND dp.companyid = t.companyid LIMIT 1) AS indepot_name");
		sql.append(" FROM t_sell_allocate t");
		sql.append(" WHERE ac_id = :ac_id");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("ac_id", ac_id),
					new BeanPropertyRowMapper<>(T_Sell_Allocate.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public T_Sell_Allocate load(String number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ac_id,ac_number,ac_date,ac_out_shop,ac_out_dp,ac_in_shop,ac_in_dp,ac_state,ac_sysdate,ac_man,");
		sql.append(" ac_receiver,ac_recedate,ac_sell_money,ac_cost_money,ac_amount,ac_in_sell_money,ac_in_cost_money,ac_remark,t.companyid,");
		sql.append(" (SELECT sp_name FROM t_base_shop sp WHERE sp_code = ac_out_shop AND sp.companyid = t.companyid LIMIT 1) AS outshop_name,");
		sql.append(" (SELECT sp_name FROM t_base_shop sp WHERE sp_code = ac_in_shop AND sp.companyid = t.companyid LIMIT 1) AS inshop_name,");
		sql.append(" (SELECT dp_name FROM t_base_depot dp WHERE dp_code = ac_out_dp AND dp.companyid = t.companyid LIMIT 1) AS outdepot_name,");
		sql.append(" (SELECT dp_name FROM t_base_depot dp WHERE dp_code = ac_in_dp AND dp.companyid = t.companyid LIMIT 1) AS indepot_name");
		sql.append(" FROM t_sell_allocate t");
		sql.append(" WHERE ac_number = :ac_number");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("ac_number", number).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Sell_Allocate.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public T_Sell_Allocate check(String number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ac_id,ac_number,ac_date,ac_out_shop,ac_out_dp,ac_in_shop,ac_in_dp,ac_state,ac_sysdate,ac_man,");
		sql.append(" ac_receiver,ac_recedate,ac_sell_money,ac_cost_money,ac_amount,ac_in_sell_money,ac_in_cost_money,ac_remark,t.companyid");
		sql.append(" FROM t_sell_allocate t");
		sql.append(" WHERE ac_number = :ac_number");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("ac_number", number).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Sell_Allocate.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public List<T_Sell_AllocateList> detail_list_forsavetemp(String ac_number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT acl_id,acl_number,acl_pd_code,acl_sub_code,acl_szg_code,acl_sz_code,acl_cr_code,acl_br_code,acl_amount,");
		sql.append(" acl_sell_price,acl_cost_price,acl_in_sell_price,acl_in_cost_price,acl_remark,companyid");
		sql.append(" FROM t_sell_allocatelist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND acl_number = :acl_number");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" ORDER BY acl_id ASC");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("acl_number", ac_number).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Sell_AllocateList.class));
	}
	
	@Override
	public List<T_Sell_AllocateList> detail_list_forcopy(List<Long> ids,String sp_code) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT acl_id,acl_number,acl_pd_code,acl_sub_code,acl_szg_code,acl_sz_code,acl_cr_code,acl_br_code,");
		sql.append(" acl_amount,acl_remark,t.companyid,");
		sql.append(" f_GetProductSellPrice(pd_code,:sp_code,pd.companyid) AS acl_sell_price,");
		sql.append(" f_GetProductCostPrice(pd_code,:sp_code,pd.companyid) AS acl_cost_price");
		sql.append(" FROM t_sell_allocatelist t");
		sql.append(" JOIN t_base_product pd ON pd_code = t.acl_pd_code AND pd.companyid = t.companyid");
		sql.append(" WHERE acl_id IN(:ids)");
		return namedParameterJdbcTemplate.query(sql.toString(),new MapSqlParameterSource().addValue("ids", ids).addValue("sp_code", sp_code),
				new BeanPropertyRowMapper<>(T_Sell_AllocateList.class));
	}
	
	@Override
	public List<T_Sell_AllocateList> detail_list(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT acl_id,acl_number,acl_pd_code,acl_sub_code,acl_szg_code,acl_sz_code,acl_cr_code,acl_br_code,acl_amount,");
		sql.append(" acl_sell_price,acl_cost_price,acl_in_sell_price,acl_in_cost_price,acl_remark,t.companyid,");
		sql.append(" pd_no,pd_name,pd_unit,");
		sql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = acl_cr_code AND cr.companyid = t.companyid LIMIT 1) AS cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz_code = acl_sz_code AND sz.companyid = t.companyid LIMIT 1) AS sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = acl_br_code AND br.companyid = t.companyid LIMIT 1) AS br_name");
		sql.append(" FROM t_sell_allocatelist t");
		sql.append(" JOIN t_base_product pd ON pd_code = t.acl_pd_code AND pd.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND acl_number = :acl_number");
		sql.append(" AND t.companyid = :companyid");
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY acl_id DESC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Sell_AllocateList.class));
	}
	
	@Override
	public List<T_Sell_AllocateList> detail_list_print(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT acl_id,acl_number,acl_pd_code,acl_sub_code,acl_szg_code,acl_sz_code,acl_cr_code,acl_br_code,acl_amount,");
		sql.append(" acl_sell_price,acl_cost_price,acl_in_sell_price,acl_in_cost_price,acl_remark,t.companyid,");
		sql.append(" pd_no,pd_name,pd_unit,pd_season,pd_year,");
		sql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = acl_cr_code AND cr.companyid = t.companyid LIMIT 1) AS cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz_code = acl_sz_code AND sz.companyid = t.companyid LIMIT 1) AS sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = acl_br_code AND br.companyid = t.companyid LIMIT 1) AS br_name,");
		sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = t.companyid LIMIT 1) AS bd_name,");
		sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = t.companyid LIMIT 1) AS tp_name");
		sql.append(" FROM t_sell_allocatelist t");
		sql.append(" JOIN t_base_product pd ON pd_code = t.acl_pd_code AND pd.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND acl_number = :acl_number");
		sql.append(" AND t.companyid = :companyid");
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY acl_id DESC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Sell_AllocateList.class));
	}
	
	@Override
	public List<T_Sell_AllocateList> detail_sum(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT acl_id,acl_number,acl_pd_code,acl_szg_code,SUM(ABS(acl_amount)) AS acl_amount,");
		sql.append(" acl_sell_price,acl_cost_price,acl_in_sell_price,acl_in_cost_price,acl_remark,t.companyid,");
		sql.append(" pd_no,pd_name,pd_unit,pd_season,pd_year,");
		sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = t.companyid LIMIT 1) AS bd_name,");
		sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = t.companyid LIMIT 1) AS tp_name");
		sql.append(" FROM t_sell_allocatelist t");
		sql.append(" JOIN t_base_product pd ON pd_code = t.acl_pd_code AND pd.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND acl_number = :acl_number");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY acl_pd_code");
		sql.append(" ORDER BY acl_pd_code ASC");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Sell_AllocateList.class));
	}
	
	@Override
	public List<String> detail_szgcode(Map<String,Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT DISTINCT acl_szg_code");
		sql.append(" FROM t_sell_allocatelist t ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND acl_number = :acl_number");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.queryForList(sql.toString(), params, String.class);
	}
	
	@Override
	public List<T_Sell_AllocateList> temp_list(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		
		sql.append(" SELECT acl_id,acl_pd_code,acl_sub_code,acl_szg_code,acl_sz_code,acl_cr_code,acl_br_code,acl_amount,");
		sql.append(" acl_sell_price,acl_cost_price,acl_remark,acl_us_id,t.companyid,");
		sql.append(" pd_no,pd_name,pd_unit,");
		sql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = acl_cr_code AND cr.companyid = t.companyid LIMIT 1) AS cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz_code = acl_sz_code AND sz.companyid = t.companyid LIMIT 1) AS sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = acl_br_code AND br.companyid = t.companyid LIMIT 1) AS br_name");
		sql.append(" FROM t_sell_allocatelist_temp t");
		sql.append(" JOIN t_base_product pd ON pd_code = t.acl_pd_code AND pd.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND acl_us_id = :acl_us_id");
		sql.append(" AND t.companyid = :companyid");
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY acl_id DESC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Sell_AllocateList.class));
	}
	
	@Override
	public List<T_Sell_AllocateList> temp_list_forimport(Integer us_id,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT acl_id,acl_pd_code,acl_sub_code,acl_szg_code,acl_sz_code,acl_cr_code,acl_br_code,acl_amount,");
		sql.append(" acl_sell_price,acl_cost_price,acl_remark,acl_us_id,t.companyid");
		sql.append(" FROM t_sell_allocatelist_temp t");
		sql.append(" WHERE 1=1");
		sql.append(" AND acl_us_id = :acl_us_id");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("acl_us_id", us_id).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Sell_AllocateList.class));
	}
	
	@Override
	public List<T_Sell_AllocateList> temp_list_forsave(String sp_code,Integer us_id,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT acl_id,acl_pd_code,acl_sub_code,acl_szg_code,acl_sz_code,acl_cr_code,acl_br_code,acl_amount,");
		sql.append(" acl_sell_price,acl_cost_price,acl_remark,acl_us_id,t.companyid,");
		sql.append(" f_GetProductSellPrice(pd_code,:sp_code,pd.companyid) AS acl_in_sell_price,");
		sql.append(" f_GetProductCostPrice(pd_code,:sp_code,pd.companyid) AS acl_in_cost_price");
		sql.append(" FROM t_sell_allocatelist_temp t");
		sql.append(" JOIN t_base_product pd ON pd_code = t.acl_pd_code AND pd.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND acl_us_id = :acl_us_id");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" ORDER BY acl_id ASC");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("sp_code", sp_code).addValue("acl_us_id", us_id).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Sell_AllocateList.class));
	}
	
	@Override
	public List<T_Sell_AllocateList> temp_sum(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT acl_id,acl_pd_code,acl_szg_code,SUM(ABS(acl_amount)) AS acl_amount,");
		sql.append(" acl_sell_price,acl_cost_price,acl_remark,acl_us_id,t.companyid,");
		sql.append(" pd_no,pd_name,pd_unit,");
		sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = t.companyid LIMIT 1) AS bd_name,");
		sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = t.companyid LIMIT 1) AS tp_name");
		sql.append(" FROM t_sell_allocatelist_temp t");
		sql.append(" JOIN t_base_product pd ON pd_code = t.acl_pd_code AND pd.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND acl_us_id = :acl_us_id");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY acl_pd_code");
		sql.append(" ORDER BY acl_pd_code ASC");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Sell_AllocateList.class));
	}
	
	@Override
	public List<String> temp_szgcode(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT DISTINCT acl_szg_code");
		sql.append(" FROM t_sell_allocatelist_temp t ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND acl_us_id = :acl_us_id");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.queryForList(sql.toString(), params, String.class);
	}
	
	@Override
	public T_Sell_AllocateList temp_loadBySubCode(String sub_code, Integer us_id, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT acl_id,acl_pd_code,acl_sub_code,acl_szg_code,acl_sz_code,acl_cr_code,acl_br_code,acl_amount,");
		sql.append(" acl_sell_price,acl_cost_price,acl_remark,acl_us_id,t.companyid");
		sql.append(" FROM t_sell_allocatelist_temp t");
		sql.append(" WHERE 1=1");
		sql.append(" AND acl_sub_code = :acl_sub_code");
		sql.append(" AND acl_us_id = :acl_us_id");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(),
					new MapSqlParameterSource().addValue("acl_sub_code", sub_code).addValue("acl_us_id", us_id).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Sell_AllocateList.class));
		}catch(Exception e){
			return null;
		}
	}
	
	@Override
	public Integer count_product(Map<String, Object> param) {
		Object searchContent = param.get("searchContent");
		Object alreadyExist = param.get("alreadyExist");
		Object exactQuery = param.get("exactQuery");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT count(1)");
		sql.append(" FROM (");
		sql.append(" SELECT 1 FROM t_base_product t");
		sql.append(" LEFT JOIN t_sell_allocatelist_temp acl ON acl_pd_code = pd_code AND acl.companyid = t.companyid AND acl_us_id = :us_id");
		sql.append(" WHERE 1 = 1");
		if(StringUtil.isNotEmpty(searchContent)){
			if("1".equals(exactQuery)){
				sql.append(" AND (t.pd_name = :searchContent OR t.pd_no = :searchContent)");
			}else {
				sql.append(" AND (instr(t.pd_name,:searchContent)>0 OR instr(t.pd_spell,:searchContent)>0 OR instr(t.pd_no,:searchContent)>0)");
			}
        }
		if("1".equals(alreadyExist)){
			sql.append(" AND acl_id IS NOT NULL ");
		}
		sql.append(" and t.companyid=:companyid");
		sql.append(" GROUP BY pd_code)t");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), param, Integer.class);
	}

	@Override
	public List<T_Base_Product> list_product(Map<String, Object> param) {
		Object sidx = param.get(CommonUtil.SIDX);
		Object sord = param.get(CommonUtil.SORD);
		Object searchContent = param.get("searchContent");
		Object alreadyExist = param.get("alreadyExist");
		Object exactQuery = param.get("exactQuery");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT pd_id,pd_code,pd_no,pd_name,IF(acl_id IS NULL,0,1) AS exist");
		sql.append(" FROM t_base_product t");
		sql.append(" LEFT JOIN t_sell_allocatelist_temp acl ON acl_pd_code = pd_code AND acl.companyid = t.companyid AND acl_us_id = :us_id");
		sql.append(" WHERE 1 = 1");
		if(StringUtil.isNotEmpty(searchContent)){
			if("1".equals(exactQuery)){
				sql.append(" AND (t.pd_name = :searchContent OR t.pd_no = :searchContent)");
			}else {
				sql.append(" AND (instr(t.pd_name,:searchContent)>0 OR instr(t.pd_spell,:searchContent)>0 OR instr(t.pd_no,:searchContent)>0)");
			}
        }
		if("1".equals(alreadyExist)){
			sql.append(" AND acl_id IS NOT NULL ");
		}
		sql.append(" and t.companyid=:companyid");
		sql.append(" GROUP BY pd_code");
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY pd_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Base_Product.class));
	}
	
	@Override
	public T_Base_Product load_product(String pd_code,String sp_code,Integer companyid){
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT pd.pd_id,pd.pd_code,pd_no,pd_name,pd_szg_code,pd_unit,pd_year,pd_season,");
		sql.append(" f_GetProductSellPrice(pd_code,:sp_code,pd.companyid) AS pd_sell_price,");
		sql.append(" f_GetProductCostPrice(pd_code,:sp_code,pd.companyid) AS pd_cost_price,");
		sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = pd.companyid LIMIT 1) AS pd_bd_name,");
		sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = pd.companyid LIMIT 1) AS pd_tp_name");
		sql.append(" ,(SELECT pdm_img_path FROM t_base_product_img pdm WHERE pdm_pd_code = pd.pd_code AND pdm.companyid = pd.companyid LIMIT 1) AS pdm_img_path");
		sql.append(" FROM t_base_product pd");
		sql.append(" WHERE 1=1");
		sql.append(" AND pd.pd_code = :pd_code");
		sql.append(" AND pd.companyid = :companyid");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("pd_code", pd_code).addValue("sp_code", sp_code).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Base_Product.class));
		}catch(Exception e){
			return null;
		}
	}
	
	@Override
	public Map<String, Object> load_product_size(Map<String,Object> params) {
		Map<String, Object> resultMap = new HashMap<String, Object>();
		//1.查询尺码信息
		List<T_Base_Size> sizes = namedParameterJdbcTemplate.query(getSizeSQL(), params, new BeanPropertyRowMapper<>(T_Base_Size.class));
		resultMap.put("sizes",sizes);
		//2.获取颜色杯型信息
		List<ProductDto> inputs = namedParameterJdbcTemplate.query(getColorBraSQL(), params, new BeanPropertyRowMapper<>(ProductDto.class));
		resultMap.put("inputs",inputs);
		//3.已录入数量
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT acl_sz_code AS sz_code,acl_cr_code AS cr_code,acl_br_code AS br_code,acl_amount AS amount");
		sql.append(" FROM t_sell_allocatelist_temp");
		sql.append(" WHERE 1=1");
		sql.append(" AND acl_pd_code = :pd_code");
		sql.append(" AND acl_us_id = :us_id");
		sql.append(" AND companyid = :companyid");
		List<ProductDto> temps = namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(ProductDto.class));
		resultMap.put("temps",temps);
		//4.库存数量
		List<ProductDto> stocks = namedParameterJdbcTemplate.query(getStockSQL(), params, new BeanPropertyRowMapper<>(ProductDto.class));
		resultMap.put("stocks",stocks);
		return resultMap;
	}
	
	@Override
	public void temp_save(List<T_Sell_AllocateList> temps) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_sell_allocatelist_temp");
		sql.append(" (acl_pd_code,acl_sub_code,acl_sz_code,acl_szg_code,acl_cr_code,acl_br_code,acl_amount,");
		sql.append(" acl_sell_price,acl_cost_price,acl_remark,acl_us_id,companyid)");
		sql.append(" VALUES");
		sql.append(" (:acl_pd_code,:acl_sub_code,:acl_sz_code,:acl_szg_code,:acl_cr_code,:acl_br_code,:acl_amount,");
		sql.append(" :acl_sell_price,:acl_cost_price,:acl_remark,:acl_us_id,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps.toArray()));
	}
	
	@Override
	public void temp_save(T_Sell_AllocateList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_sell_allocatelist_temp");
		sql.append(" (acl_pd_code,acl_sub_code,acl_sz_code,acl_szg_code,acl_cr_code,acl_br_code,acl_amount,");
		sql.append(" acl_sell_price,acl_cost_price,acl_remark,acl_us_id,companyid)");
		sql.append(" VALUES");
		sql.append(" (:acl_pd_code,:acl_sub_code,:acl_sz_code,:acl_szg_code,:acl_cr_code,:acl_br_code,:acl_amount,");
		sql.append(" :acl_sell_price,:acl_cost_price,:acl_remark,:acl_us_id,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp),holder);
		temp.setAcl_id(holder.getKey().intValue());
	}
	
	@Override
	public void temp_update(List<T_Sell_AllocateList> temps) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_sell_allocatelist_temp");
		sql.append(" SET acl_amount = :acl_amount");
		sql.append(" WHERE 1=1");
		sql.append(" AND acl_sub_code = :acl_sub_code");
		sql.append(" AND acl_us_id = :acl_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps.toArray()));
	}
	
	@Override
	public void temp_updateById(List<T_Sell_AllocateList> temps) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_sell_allocatelist_temp");
		sql.append(" SET acl_amount = :acl_amount");
		sql.append(" WHERE 1=1");
		sql.append(" AND acl_id = :acl_id");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps.toArray()));
	}
	
	@Override
	public void temp_update(T_Sell_AllocateList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_sell_allocatelist_temp");
		sql.append(" SET acl_amount = :acl_amount");
		sql.append(" WHERE 1=1");
		sql.append(" AND acl_id = :acl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}
	
	@Override
	public void temp_updateRemarkById(T_Sell_AllocateList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_sell_allocatelist_temp");
		sql.append(" SET acl_remark = :acl_remark");
		sql.append(" WHERE 1=1");
		sql.append(" AND acl_id = :acl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}
	
	@Override
	public void temp_updateRemarkByPdCode(T_Sell_AllocateList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_sell_allocatelist_temp");
		sql.append(" SET acl_remark = :acl_remark");
		sql.append(" WHERE 1=1");
		sql.append(" AND acl_pd_code = :acl_pd_code");
		sql.append(" AND acl_us_id = :acl_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}
	
	@Override
	public void temp_del(List<T_Sell_AllocateList> temps) {
		StringBuffer sql = new StringBuffer("");
		sql.append("DELETE FROM t_sell_allocatelist_temp");
		sql.append(" WHERE 1=1");
		sql.append(" AND acl_sub_code = :acl_sub_code");
		sql.append(" AND acl_us_id = :acl_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps.toArray()));
	}
	
	@Override
	public void temp_del(Integer acl_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_sell_allocatelist_temp");
		sql.append(" WHERE acl_id=:acl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("acl_id", acl_id));
	}
	
	@Override
	public void temp_delByPiCode(T_Sell_AllocateList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("DELETE FROM t_sell_allocatelist_temp");
		sql.append(" WHERE 1=1");
		sql.append(" AND acl_pd_code = :acl_pd_code");
		if(StringUtil.isNotEmpty(temp.getAcl_cr_code())){
			sql.append(" AND acl_cr_code = :acl_cr_code");
		}
		if(StringUtil.isNotEmpty(temp.getAcl_br_code())){
			sql.append(" AND acl_br_code = :acl_br_code");
		}
		sql.append(" AND acl_us_id = :acl_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}
	
	@Override
	public void temp_clear(Integer us_id,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("DELETE FROM t_sell_allocatelist_temp");
		sql.append(" WHERE 1=1");
		sql.append(" AND acl_us_id = :acl_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(),
				new MapSqlParameterSource().addValue("acl_us_id", us_id).addValue("companyid", companyid));
	}
	
	@Override
	public List<BarcodeImportDto> temp_listByImport(List<String> barCodes,String sp_code,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT DISTINCT bc_pd_code,bc_subcode,bc_barcode,bc_size,pd_szg_code,bc_color,bc_bra,");
		sql.append(" f_GetProductSellPrice(pd_code,:sp_code,pd.companyid) AS sell_price,");
		sql.append(" f_GetProductCostPrice(pd_code,:sp_code,pd.companyid) AS cost_price");
		sql.append(" FROM t_base_barcode bc");
		sql.append(" JOIN t_base_product pd ON pd.pd_code = bc_pd_code AND pd.companyid = bc.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND bc_barcode IN(:barcode)");
		sql.append(" AND bc.companyid = :companyid");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("barcode", barCodes).addValue("sp_code", sp_code).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(BarcodeImportDto.class));
	}
	
	@Override
	public void save(T_Sell_Allocate allocate, List<T_Sell_AllocateList> details) {
		String prefix = CommonUtil.NUMBER_PREFIX_SELL_ALLOCATE + DateUtil.getYearMonthDateYYYYMMDD();
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT CONCAT(:prefix,f_addnumber(MAX(ac_number))) AS new_number");
		sql.append(" FROM t_sell_allocate");
		sql.append(" WHERE 1=1");
		sql.append(" AND INSTR(ac_number,:prefix) > 0");
		sql.append(" AND companyid = :companyid");
		String new_number = namedParameterJdbcTemplate.queryForObject(sql.toString(), 
				new MapSqlParameterSource().addValue("prefix", prefix).addValue("companyid", allocate.getCompanyid()), String.class);
		allocate.setAc_number(new_number);
		sql.setLength(0);
		sql.append(" INSERT INTO t_sell_allocate");
		sql.append(" (ac_number,ac_date,ac_out_shop,ac_out_dp,ac_in_shop,ac_in_dp,ac_state,ac_sysdate,ac_man,");
		sql.append(" ac_receiver,ac_recedate,ac_sell_money,ac_cost_money,ac_amount,ac_in_sell_money,ac_in_cost_money,ac_remark,companyid)");
		sql.append(" VALUES");
		sql.append(" (:ac_number,:ac_date,:ac_out_shop,:ac_out_dp,:ac_in_shop,:ac_in_dp,:ac_state,:ac_sysdate,:ac_man,");
		sql.append(" :ac_receiver,:ac_recedate,:ac_sell_money,:ac_cost_money,:ac_amount,:ac_in_sell_money,:ac_in_cost_money,:ac_remark,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(allocate),holder);
		allocate.setAc_id(holder.getKey().intValue());
		for(T_Sell_AllocateList item:details){
			item.setAcl_number(allocate.getAc_number());
		}
		sql.setLength(0);
		sql.append(" INSERT INTO t_sell_allocatelist");
		sql.append(" (acl_number,acl_pd_code,acl_sub_code,acl_szg_code,acl_sz_code,acl_cr_code,acl_br_code,acl_amount,");
		sql.append(" acl_sell_price,acl_cost_price,acl_in_sell_price,acl_in_cost_price,acl_remark,companyid)");
		sql.append(" VALUES");
		sql.append(" (:acl_number,:acl_pd_code,:acl_sub_code,:acl_szg_code,:acl_sz_code,:acl_cr_code,:acl_br_code,:acl_amount,");
		sql.append(" :acl_sell_price,:acl_cost_price,:acl_in_sell_price,:acl_in_cost_price,:acl_remark,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(details.toArray()));
	}
	
	@Override
	public void update(T_Sell_Allocate allocate, List<T_Sell_AllocateList> details) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_sell_allocate");
		sql.append(" SET ac_date=:ac_date");
		sql.append(" ,ac_out_shop=:ac_out_shop");
		sql.append(" ,ac_in_shop=:ac_in_shop");
		sql.append(" ,ac_out_dp=:ac_out_dp");
		sql.append(" ,ac_in_dp=:ac_in_dp");
		sql.append(" ,ac_man=:ac_man");
		sql.append(" ,ac_amount=:ac_amount");
		sql.append(" ,ac_sell_money=:ac_sell_money");
		sql.append(" ,ac_cost_money=:ac_cost_money");
		sql.append(" ,ac_in_sell_money=:ac_in_sell_money");
		sql.append(" ,ac_in_cost_money=:ac_in_cost_money");
		sql.append(" ,ac_remark=:ac_remark");
		sql.append(" ,ac_state=:ac_state");
		sql.append(" WHERE ac_id=:ac_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(allocate));
		for(T_Sell_AllocateList item:details){
			item.setAcl_number(allocate.getAc_number());
		}
		sql.setLength(0);
		sql.append(" INSERT INTO t_sell_allocatelist");
		sql.append(" (acl_number,acl_pd_code,acl_sub_code,acl_szg_code,acl_sz_code,acl_cr_code,acl_br_code,acl_amount,");
		sql.append(" acl_sell_price,acl_cost_price,acl_in_sell_price,acl_in_cost_price,acl_remark,companyid)");
		sql.append(" VALUES");
		sql.append(" (:acl_number,:acl_pd_code,:acl_sub_code,:acl_szg_code,:acl_sz_code,:acl_cr_code,:acl_br_code,:acl_amount,");
		sql.append(" :acl_sell_price,:acl_cost_price,:acl_in_sell_price,:acl_in_cost_price,:acl_remark,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(details.toArray()));
	}
	
	@Override
	public void updateSend(T_Sell_Allocate allocate) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_sell_allocate");
		sql.append(" SET ac_state=:ac_state");
		sql.append(" ,ac_out_dp=:ac_out_dp");
		sql.append(" WHERE ac_id=:ac_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(allocate));
	}
	
	@Override
	public void updateReceive(T_Sell_Allocate allocate) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_sell_allocate");
		sql.append(" SET ac_state=:ac_state");
		sql.append(" ,ac_in_dp=:ac_in_dp");
		sql.append(" ,ac_receiver=:ac_receiver");
		sql.append(" ,ac_recedate=:ac_recedate");
		sql.append(" WHERE ac_id=:ac_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(allocate));
	}
	
	@Override
	public void updateState(T_Sell_Allocate allocate) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_sell_allocate");
		sql.append(" SET ac_state=:ac_state");
		sql.append(" WHERE ac_id=:ac_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(allocate));
	}
	
	@Override
	public List<T_Stock_DataBill> listStock(String number,String dp_code, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT acl_pd_code AS sd_pd_code,acl_sub_code AS sd_code,acl_cr_code AS sd_cr_code,acl_sz_code AS sd_sz_code,");
		sql.append(" acl_br_code AS sd_br_code,SUM(ABS(acl_amount)) AS bill_amount,sd_id,sd_amount");
		sql.append(" FROM t_sell_allocatelist acl");
		sql.append(" LEFT JOIN t_stock_data sd ON acl_sub_code = sd_code AND acl.companyid = sd.companyid AND sd.sd_dp_code = :dp_code");
		sql.append(" WHERE 1=1");
		sql.append(" AND acl_number = :acl_number");
		sql.append(" AND acl.companyid = :companyid");
		sql.append(" GROUP BY acl_sub_code");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("acl_number", number).addValue("dp_code", dp_code).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Stock_DataBill.class));
	}
	
	@Override
	public void del(String ac_number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_sell_allocate");
		sql.append(" WHERE ac_number=:ac_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("ac_number", ac_number).addValue("companyid", companyid));
		sql.setLength(0);
		sql.append(" DELETE FROM t_sell_allocatelist");
		sql.append(" WHERE acl_number=:acl_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("acl_number", ac_number).addValue("companyid", companyid));
	}
	
	@Override
	public void deleteList(String ac_number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_sell_allocatelist");
		sql.append(" WHERE acl_number=:acl_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("acl_number", ac_number).addValue("companyid", companyid));
	}
	
	@Override
	public Integer countReport(Map<String, Object> params) {
		Object ac_state = params.get("ac_state");
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object ac_out_shop = params.get("ac_out_shop");
		Object ac_in_shop = params.get("ac_in_shop");
		Object ac_out_dp = params.get("ac_out_dp");
		Object ac_in_dp = params.get("ac_in_dp");
		Object ac_man = params.get("ac_man");
		Object ac_number = params.get("ac_number");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1)");
		sql.append(" FROM t_sell_allocate t");
		sql.append(" JOIN t_sell_allocatelist acl ON acl_number = ac_number AND acl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = acl_pd_code AND pd.companyid = acl.companyid");
		sql.append(" WHERE 1=1");
		if (StringUtil.isNotEmpty(ac_state)) {
			sql.append(" AND ac_state = :ac_state ");
		}
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND ac_sysdate >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND ac_sysdate <= :enddate ");
		}
		if (StringUtil.isNotEmpty(ac_out_shop)) {
			sql.append(" AND ac_out_shop = :ac_out_shop ");
		}
		if (StringUtil.isNotEmpty(ac_in_shop)) {
			sql.append(" AND ac_in_shop = :ac_in_shop ");
		}
		if (StringUtil.isNotEmpty(ac_out_dp)) {
			sql.append(" AND ac_out_dp = :ac_out_dp ");
		}
		if (StringUtil.isNotEmpty(ac_in_dp)) {
			sql.append(" AND ac_in_dp = :ac_in_dp ");
		}
		if (StringUtil.isNotEmpty(ac_man)) {
			sql.append(" AND ac_man = :ac_man ");
		}
		if (StringUtil.isNotEmpty(ac_number)) {
			sql.append(" AND INSTR(ac_number,:ac_number) > 0 ");
		}
		sql.append(" AND (ac_out_shop = :shop_code OR ac_in_shop = :shop_code)");
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}

	@Override
	public List<SellAllocateReportDto> listReport(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		Object ac_state = params.get("ac_state");
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object ac_out_shop = params.get("ac_out_shop");
		Object ac_in_shop = params.get("ac_in_shop");
		Object ac_out_dp = params.get("ac_out_dp");
		Object ac_in_dp = params.get("ac_in_dp");
		Object ac_man = params.get("ac_man");
		Object ac_number = params.get("ac_number");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ac_id,ac_number,ac_date,ac_out_shop,ac_in_shop,ac_state,ac_sysdate,ac_man,ac_receiver,ac_recedate,");
		sql.append(" (SELECT sp_name FROM t_base_shop sp WHERE sp_code = ac_out_shop AND sp.companyid = t.companyid LIMIT 1) AS outshop_name,");
		sql.append(" (SELECT sp_name FROM t_base_shop sp WHERE sp_code = ac_in_shop AND sp.companyid = t.companyid LIMIT 1) AS inshop_name,");
		sql.append(" pd_no,pd_name,pd_unit,acl_id,acl_amount,acl_sell_price,acl_in_sell_price,");
		sql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = acl_cr_code AND cr.companyid = t.companyid LIMIT 1) AS cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz_code = acl_sz_code AND sz.companyid = t.companyid LIMIT 1) AS sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = acl_br_code AND br.companyid = t.companyid LIMIT 1) AS br_name");
		sql.append(" FROM t_sell_allocate t");
		sql.append(" JOIN t_sell_allocatelist acl ON acl_number = ac_number AND acl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = acl_pd_code AND pd.companyid = acl.companyid");
		sql.append(" WHERE 1 = 1");
		if (StringUtil.isNotEmpty(ac_state)) {
			sql.append(" AND ac_state = :ac_state ");
		}
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND ac_sysdate >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND ac_sysdate <= :enddate ");
		}
		if (StringUtil.isNotEmpty(ac_out_shop)) {
			sql.append(" AND ac_out_shop = :ac_out_shop ");
		}
		if (StringUtil.isNotEmpty(ac_in_shop)) {
			sql.append(" AND ac_in_shop = :ac_in_shop ");
		}
		if (StringUtil.isNotEmpty(ac_out_dp)) {
			sql.append(" AND ac_out_dp = :ac_out_dp ");
		}
		if (StringUtil.isNotEmpty(ac_in_dp)) {
			sql.append(" AND ac_in_dp = :ac_in_dp ");
		}
		if (StringUtil.isNotEmpty(ac_man)) {
			sql.append(" AND ac_man = :ac_man ");
		}
		if (StringUtil.isNotEmpty(ac_number)) {
			sql.append(" AND INSTR(ac_number,:ac_number) > 0 ");
		}
		sql.append(" AND (ac_out_shop = :shop_code OR ac_in_shop = :shop_code)");
		sql.append(" AND t.companyid=:companyid");
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY ac_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(SellAllocateReportDto.class));
	}
	
}
